﻿<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="原料欠款情况表.aspx.cs" Inherits="syc.Sale.原料欠款情况表" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<html >
<head runat="server">
    <title></title>
</head>
<body>
<script runat ="server">
    protected double a1;
    protected double a2;
    protected double a3;
    protected double a4;
     protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Button_Query_Click(null, null);      
                RiQi.Text = DateTime.Today.ToString("yyyy年M月d日");
                RiQi2.Text = DateTime.Today.ToString("yyyy年M月d日");
            }         
        }     
      
       protected void Button_Query_Click(object sender, EventArgs e)
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter("select top 1 当期日期 from 原料欠款记录", Conn);
            sda.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.Label1.Text = "前期截止日期:" + ((DateTime)ds.Tables[0].Rows[0][0]).ToString("yyyy年MM月dd日"); 
            Conn.Close();
        }
        protected void Button_Update_Click(object sender, EventArgs e)
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            string sql = @"
            update 原料欠款记录 set 欠款金额=[dbo].[更新原料欠款](供货ID,@d1,@d2),当期日期=@d2 where 供货ID in (select ID from 原料供货单位 left join 原料欠款记录 on 原料欠款记录.供货ID=原料供货单位.ID where 欠款金额 is not null)
            update 原料付款记录 set 付款金额=0  
            ";
            sql = sql.Replace("@d1", "'" + DateTime.Parse(this.RiQi.Text).ToString() + "'");
            sql = sql.Replace("@d2", "'" + DateTime.Parse(this.RiQi2.Text).ToString() + "'");
            Cmd.CommandText = sql;
            int i = Cmd.ExecuteNonQuery();
            if (i < 0)
            {
                this.Message.Text = "更新失败!";
            }
            else
            {
                this.Message.Text = "更新成功!";                
            }           
            Cmd.Clone();
            Conn.Close();
            
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string part1 =
               @"<html xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<meta http-equiv=Content-Type content='text/html; charset=utf-8'>
<!--[if gte mso 9]><xml>
 <o:DocumentProperties>
  <o:Author>Chinese User</o:Author>
  <o:LastAuthor>Chinese User</o:LastAuthor>
  <o:LastPrinted>2011-03-14T04:06:54Z</o:LastPrinted>
  <o:Created>2010-05-26T08:35:24Z</o:Created>
  <o:LastSaved>2011-03-14T04:07:01Z</o:LastSaved>
  <o:Company>Chinese ORG</o:Company>
  <o:Version>11.9999</o:Version>
 </o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
	{mso-displayed-decimal-separator:'\.';
	mso-displayed-thousand-separator:'\,';}
@page
	{margin:.27in .75in 1.0in .75in;
	mso-header-margin:.5in;
	mso-footer-margin:.5in;
	mso-page-orientation:landscape;}
tr
	{mso-height-source:auto;
	mso-ruby-visibility:none;}
col
	{mso-width-source:auto;
	mso-ruby-visibility:none;}
br
	{mso-data-placement:same-cell;}
.style0
	{mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	white-space:nowrap;
	mso-rotate:0;
	mso-background-source:auto;
	mso-pattern:auto;
	color:windowtext;
	font-size:12.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:宋体;
	mso-generic-font-family:auto;
	mso-font-charset:134;
	border:none;
	mso-protection:locked visible;
	mso-style-name:常规;
	mso-style-id:0;}
td
	{mso-style-parent:style0;
	padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:windowtext;
	font-size:12.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:宋体;
	mso-generic-font-family:auto;
	mso-font-charset:134;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border:none;
	mso-background-source:auto;
	mso-pattern:auto;
	mso-protection:locked visible;
	white-space:nowrap;
	mso-rotate:0;}
.xl24
	{mso-style-parent:style0;
	font-size:16.0pt;
	font-weight:700;
	text-align:center;}
.xl25
	{mso-style-parent:style0;
	font-size:16.0pt;
	font-weight:700;}
.xl26
	{mso-style-parent:style0;
	font-size:10.0pt;
	text-align:center;
    mso-number-format:'\#\,\#\#0\.00_\)\;\[Red\]\\\(\#\,\#\#0\.00\\\)';	
	border:.5pt solid windowtext;}
.xl27
	{mso-style-parent:style0;
	font-size:10.0pt;    
	border:.5pt solid windowtext;    	
}
.xl28
	{mso-style-parent:style0;
	font-size:10.0pt;  
	text-align:center;	  
	border:.5pt solid windowtext;    	
}
ruby
	{ruby-align:left;}
rt
	{color:windowtext;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:宋体;
	mso-generic-font-family:auto;
	mso-font-charset:134;
	mso-char-type:none;
	display:none;}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:PaperSizeIndex>9</x:PaperSizeIndex>
      <x:HorizontalResolution>600</x:HorizontalResolution>
      <x:VerticalResolution>600</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>10</x:ActiveRow>
       <x:ActiveCol>2</x:ActiveCol>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet2</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet3</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>8100</x:WindowHeight>
  <x:WindowWidth>12780</x:WindowWidth>
  <x:WindowTopX>360</x:WindowTopX>
  <x:WindowTopY>90</x:WindowTopY>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=1406 style='border-collapse:
 collapse;table-layout:fixed;width:1054pt'>
 <col width=40 style='mso-width-source:userset;mso-width-alt:1280;width:30pt'>
 <col width=252 style='mso-width-source:userset;mso-width-alt:8064;width:189pt'>
 <col width=175 style='mso-width-source:userset;mso-width-alt:5600;width:131pt'>
 <col width=193 style='mso-width-source:userset;mso-width-alt:6176;width:145pt'>
 <col width=159 style='mso-width-source:userset;mso-width-alt:5088;width:119pt'>
 <col width=155 style='mso-width-source:userset;mso-width-alt:4960;width:116pt'>
 <col width=72 span=6 style='width:54pt'>
 <tr height=27 style='height:20.25pt'>
  <td colspan=6 height=27 class=xl24 width=974 style='height:20.25pt;
  width:730pt'>@填报日期原材料欠款情况</td>
  <td class=xl25 width=72 style='width:54pt'></td>
  <td class=xl25 width=72 style='width:54pt'></td>
  <td class=xl25 width=72 style='width:54pt'></td>
  <td class=xl25 width=72 style='width:54pt'></td>
  <td class=xl25 width=72 style='width:54pt'></td>
  <td class=xl25 width=72 style='width:54pt'></td>
 </tr>
 <tr height=25 style='mso-height-source:userset;height:18.75pt'>
  <td height=25 class=xl26 style='height:18.75pt'>序号</td>
  <td class=xl26 style='border-left:none'>供货单位</td>
  <td class=xl26 style='border-left:none'>期初欠款金额(元)</td>
  <td class=xl26 style='border-left:none'>本期挂帐合计(元)</td>
  <td class=xl26 style='border-left:none'>
  <form id=form1 method=post name=form1 action=原料欠款情况表.aspx>
  本期付款金额(元)
  </form>
  </td>
  <td class=xl26 style='border-left:none'>
  <form id=form1 method=post name=form1 action=原料欠款情况表.aspx>
  本期欠款金额(元)
  </form>
  </td>
  <td colspan=6 style='mso-ignore:colspan'></td>
 </tr>";
            string part3 = @"
 
<![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=40 style='width:30pt'></td>
  <td width=252 style='width:189pt'></td>
  <td width=175 style='width:131pt'></td>
  <td width=193 style='width:145pt'></td>
  <td width=159 style='width:119pt'></td>
  <td width=155 style='width:116pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
 </tr>
 <![endif]>
</table>";
            Response.Charset = "utf-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("原料欠款情况") + ".xls\"");
            Response.Write(part1.Replace("@填报日期", this.RiQi.Text));

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            string sql = @"select  ID,isnull(详细名称,名称) as 名称,欠款金额,[dbo].[原料本期挂帐](ID,@d1,@d2),[dbo].[原料本期付款](ID,@d1,@d2) from 原料供货单位 left join 原料欠款记录 on 原料欠款记录.供货ID=原料供货单位.ID where 欠款金额 is not null order by 欠款金额 desc";
            sql = sql.Replace("@d1", "'" + DateTime.Parse(this.RiQi.Text).ToString() + "'");
            sql = sql.Replace("@d2", "'" + DateTime.Parse(this.RiQi2.Text).ToString() + "'");
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter(sql, Conn);
            sda.Fill(ds);
            DataTable dt = ds.Tables[0];
            int count = dt.Rows.Count;
   
            if (count != 0)
            {
                for (int i = 0; i < count; i++)
                {
                     Response.Write("<tr height=23 style='mso-height-source:userset;height:17.25pt'><td height=23 class=xl27 style='height:17.25pt;border-top:none'>" + (i + 1) + "</td>");
                     Response.Write("<td class=xl27 style='border-top:none;border-left:none' x:str='" + dt.Rows[i][1].ToString() + "'>" + dt.Rows[i][1] + "</td>");
                     Response.Write("<td class=xl26 style='border-top:none;border-left:none' x:num='" + dt.Rows[i][2] + "'>" + dt.Rows[i][2] + "</td>");
                     Response.Write("<td class=xl26 style='border-top:none;border-left:none' x:num='" + dt.Rows[i][3] + "'>" + dt.Rows[i][3] + "</td>");
                     Response.Write("<td class=xl26 style='border-top:none;border-left:none' x:num='" + dt.Rows[i][4] + "'>" + dt.Rows[i][4] + "</td>");
                     Response.Write("<td class=xl26 style='border-top:none;border-left:none' x:num='" + (double.Parse(dt.Rows[i][2].ToString()) + double.Parse(dt.Rows[i][3].ToString()) - double.Parse(dt.Rows[i][4].ToString())) + "'>" + (double.Parse(dt.Rows[i][2].ToString()) + double.Parse(dt.Rows[i][3].ToString()) - double.Parse(dt.Rows[i][4].ToString())) + "</td>");
                     Response.Write("<td colspan=6 style='mso-ignore:colspan'></td> ");
                     a1 += double.Parse(dt.Rows[i][2].ToString());
                     a2 += double.Parse(dt.Rows[i][3].ToString());
                     a3 += double.Parse(dt.Rows[i][4].ToString());
                     a4 += double.Parse(dt.Rows[i][2].ToString()) + double.Parse(dt.Rows[i][3].ToString()) - double.Parse(dt.Rows[i][4].ToString());
                }
                Response.Write("<tr height=19 style='height:14.25pt'><td colspan=2 height=19 class=xl28 style='height:14.25pt'>合计</td>");
                Response.Write("<td class=xl26 style='border-top:none;border-left:none' x:num='" + a1 + "'>'"+ a1 + "'</td>");
                Response.Write("<td class=xl26 style='border-top:none;border-left:none' x:num='" + a2 + "'>'" + a2 + "'</td>");
                Response.Write("<td class=xl26 style='border-top:none;border-left:none' x:num='" + a3 + "'>'" + a3 + "'</td>");
                Response.Write("<td class=xl26 style='border-top:none;border-left:none' x:num='" + a4 + "'>'" + a4 + "'</td>");
                Response.Write("<td colspan=6 style='mso-ignore:colspan'></td> ");  
            }
            Conn.Close();
            Response.Write(part3);
            Response.End();
        }
    
    </script>
 <script type="text/javascript" language="javascript" src="/common.js"></script>
    <form id="form1" runat="server">
    <div id="wintop" class="wintop">
        <span lang="zh-cn">原料欠款情况表</span></div>
    <asp:Panel ID="Panel1" runat="server" CssClass="panel" Height="36px">
        <span lang="zh-cn">&nbsp;起始日期：<asp:TextBox ID="RiQi" runat="server" CssClass="intxt1"
            Width="110px"></asp:TextBox>
            <img align="middle" alt="" onclick="makecalendar(document.getElementById('RiQi').value, document.getElementById('RiQi'));"
                src="/Icons/29.png" /></asp:textbox> &nbsp;&nbsp;</span>
        <span lang="zh-cn">&nbsp;截止日期：<asp:TextBox ID="RiQi2" runat="server" CssClass="intxt1"
            Width="110px"></asp:TextBox>
            <img align="middle" alt="" onclick="makecalendar(document.getElementById('RiQi2').value, document.getElementById('RiQi2'));"
                src="/Icons/29.png" /></asp:textbox> &nbsp;&nbsp;</span>    

                <asp:Button ID="Button_Update" runat="server" Text="更 新" CssClass="button" OnClick="Button_Update_Click"/>
             
                <asp:Button ID="Button_Query"
                    runat="server" Text="查看报表" CssClass="button" UseSubmitBehavior="true" OnClick="Button_Query_Click" />
                <asp:Button ID="Button1" runat="server" class="button" OnClick="Button1_Click"
            Text="导 出" />   
         &nbsp;<asp:Label ID="Label1" runat="server"></asp:Label>     
    </asp:Panel>
    <asp:Panel ID="Panel2" runat="server" CssClass="panel2">     

        <asp:Label ID="Message" runat="server" Text="·准备就绪" CssClass="prompt" Visible="True"></asp:Label>
    </asp:Panel>
    <div align="center">
<table width="900pt" >
 <tr height=37 align="center">
  <td colspan=6 height=37  style="font-size:20px" >原材料欠款情况
 </table>
 <table width="900pt"  border="1" style="border-style:solid; border-color:Black">
 <tr height=19 style='height:14.25pt'>
  <td height=19  style='height:14.25pt'align="center">序号</td>
  <td  align="center">供货单位</td>
  <td  align="center">期初欠款金额(元)</td>
  <td  align="center">本期挂帐合计(元)</td>
  <td  align="center">本期付款金额(元)</td>
  <td  align="center">本期欠款金额(元)</td> 
 </tr>
 <%
       SqlConnection Conn = new SqlConnection();
                    Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
                    Conn.Open();
                    string sql = @"select  ID,isnull(详细名称,名称) as 名称,欠款金额,[dbo].[原料本期挂帐](ID,@d1,@d2),[dbo].[原料本期付款](ID,@d1,@d2) from 原料供货单位 left join 原料欠款记录 on 原料欠款记录.供货ID=原料供货单位.ID where 欠款金额 is not null order by 欠款金额 desc";
                    sql = sql.Replace("@d1", "'"+DateTime.Parse(this.RiQi.Text).ToString()+"'");
                    sql = sql.Replace("@d2", "'" + DateTime.Parse(this.RiQi2.Text).ToString() + "'");
                    DataSet ds = new DataSet();
                    SqlDataAdapter sda = new SqlDataAdapter(sql, Conn);
                    sda.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    int count = dt.Rows.Count;                 
                    if (count != 0)
                    {
                        for (int i = 0; i < count; i++)
                        {
                            a1 += double.Parse(dt.Rows[i][2].ToString());
                            a2 += double.Parse(dt.Rows[i][3].ToString());
                            a3 += double.Parse(dt.Rows[i][4].ToString());
                            a4 += double.Parse(dt.Rows[i][2].ToString()) + double.Parse(dt.Rows[i][3].ToString()) - double.Parse(dt.Rows[i][4].ToString());
     %>              
  <tr height=19 style='height:14.25pt'>
  <td height=19  style='height:14.25pt'align="center"><%=i+1%></td>
  <td ><%=dt.Rows[i][1] %></td>
  <td  align="center"><%=dt.Rows[i][2] %></td>
  <td  align="center"><%=dt.Rows[i][3] %></td>
  <td  align="center"><%=dt.Rows[i][4] %></td>
  <td  align="center"><%=double.Parse(dt.Rows[i][2].ToString()) + double.Parse(dt.Rows[i][3].ToString()) - double.Parse(dt.Rows[i][4].ToString())%></td>
 </tr> 
 <%}
                    }     
     Conn.Close(); %>
       <tr height=19 style='height:14.25pt'>
        <td height=19  style='height:14.25pt'align="center" colspan="2">合计</td>
        <td  align="center"><%=a1 %></td>
        <td  align="center"><%=a2 %></td>
        <td  align="center"><%=a3 %></td>
        <td  align="center"><%=a4 %></td>
       </tr>                
</table>
    </div>
    </form>
</body>
   
</html>
    <script>        parent.hidesubmenu(false);</script>